package com.vegas.netspider.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;

import com.vegas.netspider.dto.MatchScore;

public class MatchScoreDbHandler extends DbHandler{

	private String insertFtSql="insert into ft_match_score (match_id,league_name,home_team_name,home_team_id,guest_team_name,guest_team_id,play_time,ht_half1_socre,gt_half1_socre,ht_half2_socre,gt_half2_socre,ht_full_socre,gt_full_socre,ht_q1_score," +
			"gt_q1_score,ht_q2_score,gt_q2_score,ht_q3_score,gt_q3_score,ht_q4_score,gt_q4_score,ht_e_score,gt_e_score,exists_more,more_result) " +
			" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	public void saveFtScore(final MatchScore score){
		if(score==null||score.getHomeTeamId()==null||score.getGuestTeamId()==null){
			return;
		}
		
		boolean exists = this.existsFtScore(score);
		if(exists){
			this.updateFtScore(score);
			return;
		}
		
		jdbcTemplate.update(insertFtSql, new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement p) throws SQLException {
				p.setInt(1, score.getMatchId()==null?0:score.getMatchId());
				p.setString(2, score.getLeagueName());
				p.setString(3, score.getHomeTeamName());
				p.setInt(4, score.getHomeTeamId());
				p.setString(5, score.getGuestTeamName());
				p.setInt(6, score.getGuestTeamId());
				p.setLong(7, score.getPlayTime());
				p.setString(8, score.getHtFirstHalfScore());
				p.setString(9, score.getGtFirstHalfScore());
				p.setString(10, score.getHtSecondHalfScore());
				p.setString(11, score.getGtSecondHalfScore());
				p.setString(12, score.getHtFullSocre());
				p.setString(13, score.getGtFullSocre());
				p.setString(14, score.getHtGuarter1Score());
				p.setString(15, score.getGtGuarter1Score());
				p.setString(16, score.getHtGuarter2Score());
				p.setString(17, score.getGtGuarter2Score());
				p.setString(18, score.getHtGuarter3Score());
				p.setString(19, score.getGtGuarter3Score());
				p.setString(20, score.getHtGuarter4Score());
				p.setString(21, score.getGtGuarter4Score());
				
				p.setString(22, score.getHtExtraScore());
				p.setString(23, score.getGtExtraScore());
				
				p.setBoolean(24, score.isExistsMore());
				p.setString(25, score.getMoreResult());
			}
		});
	}
	
	
	private String insertBkSql="insert into bk_match_score (match_id,league_name,home_team_name,home_team_id,guest_team_name,guest_team_id,play_time,ht_half1_socre,gt_half1_socre,ht_half2_socre,gt_half2_socre,ht_full_socre,gt_full_socre,ht_q1_score," +
			"gt_q1_score,ht_q2_score,gt_q2_score,ht_q3_score,gt_q3_score,ht_q4_score,gt_q4_score,ht_e_score,gt_e_score,exists_more,more_result) " +
			" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	public void saveBkScore(final MatchScore score){
		if(score==null||score.getHomeTeamId()==null||score.getGuestTeamId()==null){
			return;
		}
		
		boolean exists = this.existsBkScore(score);
		if(exists){
			this.updateBkScore(score);
			return;
		}
		

		jdbcTemplate.update(insertBkSql, new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement p) throws SQLException {
				p.setInt(1, score.getMatchId()==null?0:score.getMatchId());
				p.setString(2, score.getLeagueName());
				p.setString(3, score.getHomeTeamName());
				p.setInt(4, score.getHomeTeamId());
				p.setString(5, score.getGuestTeamName());
				p.setInt(6, score.getGuestTeamId());
				p.setLong(7, score.getPlayTime());
				p.setString(8, score.getHtFirstHalfScore());
				p.setString(9, score.getGtFirstHalfScore());
				p.setString(10, score.getHtSecondHalfScore());
				p.setString(11, score.getGtSecondHalfScore());
				p.setString(12, score.getHtFullSocre());
				p.setString(13, score.getGtFullSocre());
				p.setString(14, score.getHtGuarter1Score());
				p.setString(15, score.getGtGuarter1Score());
				p.setString(16, score.getHtGuarter2Score());
				p.setString(17, score.getGtGuarter2Score());
				p.setString(18, score.getHtGuarter3Score());
				p.setString(19, score.getGtGuarter3Score());
				p.setString(20, score.getHtGuarter4Score());
				p.setString(21, score.getGtGuarter4Score());
				
				p.setString(22, score.getHtExtraScore());
				p.setString(23, score.getGtExtraScore());
				
				p.setBoolean(24, score.isExistsMore());
				p.setString(25, score.getMoreResult());
			}
		});
	}
	
	
	private String existsFtScoreSql="select count(match_id) from ft_match_score where home_team_id=? and guest_team_id=? and play_time>? and play_time<?";
	public boolean existsFtScore(MatchScore score){
		List<Integer> list = jdbcTemplate.query(existsFtScoreSql, new Object[]{score.getHomeTeamId(),score.getGuestTeamId(),score.getPlayTime()-24*60*60*1000,score.getPlayTime()+12*60*60*1000}, new RowMapper<Integer>(){
			@Override
			public Integer mapRow(ResultSet r, int arg1) throws SQLException {
				return r.getInt(1);
			}
		});
		
		if(list==null||list.isEmpty()||list.get(0)==0){
			return false;
		}
		
		return true;
	}
	
	private String existsBkScoreSql="select count(match_id) from ft_match_score where home_team_id=? and guest_team_id=? and play_time>? and play_time<?";
	public boolean existsBkScore(MatchScore score){
		List<Integer> list = jdbcTemplate.query(existsBkScoreSql, new Object[]{score.getHomeTeamId(),score.getGuestTeamId(),score.getPlayTime()-24*60*60*1000,score.getPlayTime()+12*60*60*1000}, new RowMapper<Integer>(){
			@Override
			public Integer mapRow(ResultSet r, int arg1) throws SQLException {
				return r.getInt(1);
			}
		});
		
		if(list==null||list.isEmpty()||list.get(0)==0){
			return false;
		}
		
		return true;
	}
	
	private String updateFtSql = "update ft_match_score set play_time=?,ht_half1_socre=?,gt_half1_socre=?,ht_half2_socre=?,gt_half2_socre=?,ht_full_socre=?,gt_full_socre=?,ht_q1_score=?," +
			"gt_q1_score=?,ht_q2_score=?,gt_q2_score=?,ht_q3_score=?,gt_q3_score=?,ht_q4_score=?,gt_q4_score=?,ht_e_score=?,gt_e_score=?,exists_more=?,more_result=?" +
			" where home_team_id=? and guest_team_id=? and play_time>? and play_time<?";
	public void updateFtScore(final MatchScore score){
		jdbcTemplate.update(updateFtSql, new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement p) throws SQLException {
				p.setLong(1, score.getPlayTime());
				p.setString(2, score.getHtFirstHalfScore());
				p.setString(3, score.getGtFirstHalfScore());
				p.setString(4, score.getHtSecondHalfScore());
				p.setString(5, score.getGtSecondHalfScore());
				p.setString(6, score.getHtFullSocre());
				p.setString(7, score.getGtFullSocre());
				p.setString(8, score.getHtGuarter1Score());
				p.setString(9, score.getGtGuarter1Score());
				p.setString(10, score.getHtGuarter2Score());
				p.setString(11, score.getGtGuarter2Score());
				p.setString(12, score.getHtGuarter3Score());
				p.setString(13, score.getGtGuarter3Score());
				p.setString(14, score.getHtGuarter4Score());
				p.setString(15, score.getGtGuarter4Score());
				p.setString(16, score.getHtExtraScore());
				p.setString(17, score.getGtExtraScore());
				p.setBoolean(18, score.isExistsMore());
				p.setString(19, score.getMoreResult());
				
				p.setInt(20, score.getHomeTeamId());
				p.setInt(21, score.getGuestTeamId());
				p.setLong(22, score.getPlayTime()-24*60*60*1000);
				p.setLong(23, score.getPlayTime()+12*60*60*1000);
			}
		});
	}
	
	private String updateBkSql = "update bk_match_score set play_time=?,ht_half1_socre=?,gt_half1_socre=?,ht_half2_socre=?,gt_half2_socre=?,ht_full_socre=?,gt_full_socre=?,ht_q1_score=?," +
			"gt_q1_score=?,ht_q2_score=?,gt_q2_score=?,ht_q3_score=?,gt_q3_score=?,ht_q4_score=?,gt_q4_score=?,ht_e_score=?,gt_e_score=?,exists_more=?,more_result=?" +
			" where home_team_id=? and guest_team_id=? and play_time>? and play_time<?";
	public void updateBkScore(final MatchScore score){
		jdbcTemplate.update(updateBkSql, new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement p) throws SQLException {
				p.setLong(1, score.getPlayTime());
				p.setString(2, score.getHtFirstHalfScore());
				p.setString(3, score.getGtFirstHalfScore());
				p.setString(4, score.getHtSecondHalfScore());
				p.setString(5, score.getGtSecondHalfScore());
				p.setString(6, score.getHtFullSocre());
				p.setString(7, score.getGtFullSocre());
				p.setString(8, score.getHtGuarter1Score());
				p.setString(9, score.getGtGuarter1Score());
				p.setString(10, score.getHtGuarter2Score());
				p.setString(11, score.getGtGuarter2Score());
				p.setString(12, score.getHtGuarter3Score());
				p.setString(13, score.getGtGuarter3Score());
				p.setString(14, score.getHtGuarter4Score());
				p.setString(15, score.getGtGuarter4Score());
				p.setString(16, score.getHtExtraScore());
				p.setString(17, score.getGtExtraScore());
				p.setBoolean(18, score.isExistsMore());
				p.setString(19, score.getMoreResult());
				
				p.setInt(20, score.getHomeTeamId());
				p.setInt(21, score.getGuestTeamId());
				p.setLong(22, score.getPlayTime()-24*60*60*1000);
				p.setLong(23, score.getPlayTime()+12*60*60*1000);
			}
		});
	}
}
